package com.xj.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import jxl.BooleanCell;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Workbook;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.ZoneId;
import java.util.*;


@Slf4j
public class ExcelUtils {
    public static String EXCEL2003L = ".xls";    //2003- 版本的excel
    public static String EXCEL2007U = ".xlsx";   //2007+ 版本的excel

    public  static JSONArray easExcel(String  filename,int titlerow)  {
        Map<String,JSONArray> dataMap=new HashMap<String,JSONArray>();
        JSONArray jsonArray=  new JSONArray();
        try {
            FileInputStream fs=new FileInputStream(filename);
            org.apache.poi.ss.usermodel.Workbook wb = null;
            String fileType = filename.substring(filename.lastIndexOf("."));
            if(EXCEL2003L.equals(fileType)){
                wb = new HSSFWorkbook(fs);  //2003-
            }else if(EXCEL2007U.equals(fileType)){
                ZipSecureFile.setMinInflateRatio(-1.0d);
                wb = new XSSFWorkbook(fs);  //2007+
            }else{
                throw new Exception("解析的文件格式有误！");
            }
            //创建Excel工作薄
            if(null == wb){
                throw new Exception("创建Excel工作薄为空！");
            }

            //遍历Excel中所有的sheet
            for (int i = 0; i <wb.getNumberOfSheets(); i++) {
                Sheet sheet = wb.getSheetAt(i);
                Row titleRow=sheet.getRow(sheet.getFirstRowNum()+titlerow);
                //取消合并行
                sheet = unMergeSheet(sheet);
                //遍历当前sheet中的所有行
                for (int j = sheet.getFirstRowNum()+1+titlerow; j <= sheet.getLastRowNum(); j++) {
                    Row row = sheet.getRow(j);
                    //去掉空行及标题行
                    if(row==null||row.getFirstCellNum()==j)
                    {continue;}
                    JSONObject json=new JSONObject();
                    //遍历所有的列
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {

                        Cell cell = row.getCell(y);
                        if(cell==null) {
                            json.put(titleRow.getCell(y).getStringCellValue().trim(),"");
                        }else{
                            json.put(titleRow.getCell(y).getStringCellValue().trim(),getCellValue(cell));
                        }
                    }
                    jsonArray.add(json);
                }
            }
            if(fs!=null) {
                fs.close();
            }
            File file=new File(filename);
            file.delete();
        }  catch (Exception e) {

            log.error("crawler  export  Excel  err,{}",e);
        }
        return  jsonArray;
    }

    /**
     * 读取的excel表格表头有重复值处理
     * @param filename
     * @param titlerow
     * @return
     */
    public  static JSONArray reasExcelNoRepeat(String  filename,int titlerow)  {
        JSONArray jsonArray=  new JSONArray();
        try {
            FileInputStream fs=new FileInputStream(filename);
            org.apache.poi.ss.usermodel.Workbook wb = null;
            String fileType = filename.substring(filename.lastIndexOf(".")).toLowerCase();
            if(EXCEL2003L.equals(fileType)){
                wb = new HSSFWorkbook(fs);  //2003-
            }else if(EXCEL2007U.equals(fileType)){
                ZipSecureFile.setMinInflateRatio(-1.0d);
                wb = new XSSFWorkbook(fs);  //2007+
            }else{
                throw new Exception("解析的文件格式有误！");
            }
            //创建Excel工作薄
            if(null == wb){
                throw new Exception("创建Excel工作薄为空！");
            }

            //遍历Excel中所有的sheet
            for (int i = 0; i <wb.getNumberOfSheets(); i++) {
                Sheet sheet = wb.getSheetAt(i);
                Row titleRow=sheet.getRow(sheet.getFirstRowNum()+titlerow);

                //遍历当前sheet中的所有行
                for (int j = sheet.getFirstRowNum()+1+titlerow; j <= sheet.getLastRowNum(); j++) {
                    Row row = sheet.getRow(j);
                    //去掉空行及标题行
                    if(row==null||row.getFirstCellNum()==j)
                    {continue;}
                    JSONObject json=new JSONObject();
                    //遍历所有的列
                    for (int y = row.getFirstCellNum(); y <row.getLastCellNum(); y++) {

                        Cell cell = row.getCell(y);
                        if(cell==null) {
                            if (json.containsKey(titleRow.getCell(y).getStringCellValue())) {
                                json.put(titleRow.getCell(y).getStringCellValue() + y,"");
                            } else {
                                json.put(titleRow.getCell(y).getStringCellValue().trim(),"");
                            }
                        }else{
                            if (json.containsKey(titleRow.getCell(y).getStringCellValue())) {
                                json.put(titleRow.getCell(y).getStringCellValue() + y,getCellValue(cell));
                            } else {
                                json.put(titleRow.getCell(y).getStringCellValue().trim(),getCellValue(cell));
                            }
                        }
                    }
                    jsonArray.add(json);
                }
            }
            if(fs!=null)  fs.close();
        }  catch (Exception e) {

            log.error("crawler  export  Excel  err,{}",e);
        }
        return  jsonArray;
    }

    public  static Object getCellValue(Cell cell){
        //用String接收所有返回的值
        String value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
        switch (cell.getCellType()) {
            case  STRING:  //String类型的数据
                value =  cell.getStringCellValue();
                break;
            case  NUMERIC:      //数值类型(取值用cell.getNumericCellValue() 或cell.getDateCellValue())
                if("General".equals(cell.getCellStyle().getDataFormatString())){
               //     value = df.format(cell.getNumericCellValue());
                    value= String.valueOf(cell.getNumericCellValue());
                }else if(HSSFDateUtil.isCellDateFormatted(cell)){
                    value =DateUtils.formatDate(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()),DateUtils.yyyy_MM_dd);
                }else{
                    value = df2.format(cell.getNumericCellValue());
                }
                break;

            case BOOLEAN:  //Boolean类型
                value = String.valueOf(cell.getBooleanCellValue());
                break;


            case FORMULA : //表达式类型
                value = String.valueOf(cell.getCellFormula());
                break;

            case ERROR: //异常类型 不知道何时算异常
                value=String.valueOf(cell.getErrorCellValue());
                break;

            case BLANK:  //空，不知道何时算空
                value = "";
                break;

            default:
                value = "";
                break;
        }
        if(value==null||value.equals("")){
            value = "";
        }
        if (cell == null) {
            return "";
        }
        return value;
    }

    public static JSONArray   readExcel2003(String  filename,int titlerow){
        File file=new File(filename);
        JSONArray  jsonArray=new JSONArray();
        try{
            FileInputStream fis = new FileInputStream(file);
            Workbook rwb = Workbook.getWorkbook(fis);
            jxl.Sheet rs = rwb.getSheet(0);
            jxl.Cell[]  titleCell=rs.getRow(titlerow);
            for (int j = titlerow+1; j < rs.getRows(); j++) {
                jxl.Cell[] cells = rs.getRow(j);
                JSONObject  jsonObject=new JSONObject();
                for(int k=0;k<cells.length;k++) {
                    jxl.Cell  cell=cells[k];
                   if(cell.getType()==jxl.CellType.DATE){
                        DateCell dc=(DateCell)cell;
                       jsonObject.put(titleCell[k].getContents().trim(), dc.getDate());
                    } else if(cell.getType()==jxl.CellType.NUMBER ||cell.getType() ==jxl. CellType.NUMBER_FORMULA){
                        NumberCell nc=(NumberCell)cell;
                       jsonObject.put(titleCell[k].getContents().trim(), nc.getValue());
                    }else if(cell.getType()==jxl.CellType.EMPTY){
                       jsonObject.put(titleCell[k].getContents().trim(), null);
                   }else if(cell.getType()==jxl.CellType.ERROR){
                       jsonObject.put(titleCell[k].getContents().trim(), null);
                   }else if(cell.getType()==jxl.CellType.BOOLEAN){
                       BooleanCell booleanCell=(BooleanCell)cell;
                       jsonObject.put(titleCell[k].getContents().trim(), booleanCell.getValue());
                   }else {
                       jsonObject.put(titleCell[k].getContents().trim(), cell.getContents());
                   }
                }
                jsonArray.add(jsonObject);
            }
            if(fis!=null) {
                fis.close();
            }
            file.delete();
        }catch(Exception e){
            e.printStackTrace();
        }
        return  jsonArray;
    }

    /**
     * 拆分合并单元格
     * @param sheet
     * @return
     */
    public static Sheet unMergeSheet(Sheet sheet) {
        // 从第C1开始，拆分单元格
        CellReference ref = new CellReference("A1");
        //遍历sheet中的所有的合并区域
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            String value = "";
            CellRangeAddress region = sheet.getMergedRegion(i);
            Row firstRow = sheet.getRow(region.getFirstRow());
            Cell firstCellOfFirstRow = firstRow.getCell(region.getFirstColumn());
            //如果第一个单元格的是字符串
//            if (firstCellOfFirstRow.getCellType() == CellType.STRING) {
//                value = firstCellOfFirstRow.getStringCellValue();
//            }
            value = (String)getCellValue(firstCellOfFirstRow);
            //判断到A1才进行拆分单元格
            if (region.getFirstRow() == ref.getRow() && region.getLastColumn() == ref.getCol()) {
                sheet.removeMergedRegion(i);
            }
            //设置第一行的值为，拆分后的每一行的值
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(value);
                    }
                }
            }
        }
        return sheet;
    }

    public static void  addTitleRow(SXSSFSheet sheet, List<XLSField> xlsFieldList){
        SXSSFWorkbook workbook=   sheet.getWorkbook();
        sheet.trackAllColumnsForAutoSizing();
        SXSSFRow headRow = sheet.createRow(0);
        headRow.setHeight((short)400);
        for(int  i=0;i<xlsFieldList.size();i++){
            XLSField xlsField=xlsFieldList.get(i);
            Cell  cell= headRow.createCell(i);
            cell.setCellValue(xlsField.getLabel());
            CellStyle cellStyle=  workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setWrapText(Boolean.TRUE);
            Font  font=  workbook.createFont();
            font.setBold(Boolean.TRUE);
            font.setFontName(HSSFFont.FONT_ARIAL);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i,xlsField.getWidth()*100);
            int enterCnt=500;
            int rwsTemp =cell.getStringCellValue().split("\n").length;
            if (rwsTemp > enterCnt) {
                enterCnt = rwsTemp;
                headRow.setHeight((short)(enterCnt * 228));
            }


        }
    }

    public  static   void   writeExcel(List<XLSField> xlsFieldList, List  list , String  filename)   {
        try {
            File file = new File(filename);
            OutputStream out = new FileOutputStream(file);
            //申明一个工作薄,大于1000行时，会将以前的行写入内存
            SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
            workbook.setCompressTempFiles(true);
            SXSSFSheet sheet = workbook.createSheet(file.getName());

            //冻结首行
            sheet.createFreezePane(0, 1, 0, 1);
            addTitleRow(sheet, xlsFieldList);



            for (int k = 0; k < list.size(); k++) {
                SXSSFRow dataRow = sheet.createRow(k+1);

                Object data = list.get(k);
                for (int i = 0; i < xlsFieldList.size(); i++) {
                    XLSField xlsField = xlsFieldList.get(i);
                    int  enterCnt=500;
                    String fieldname=  xlsField.getFieldname();
                    Object value=  PropertyUtils.getProperty(data, fieldname);
                    SXSSFCell cell= dataRow.createCell(i);
                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setWrapText(Boolean.TRUE);
                    Font  font=  workbook.createFont();
                    font.setBold(Boolean.FALSE);
                    font.setFontName(HSSFFont.FONT_ARIAL);
                    cellStyle.setFont(font);
                    if(Objects.isNull(value)){
                        cell.setCellValue("");
                        cellStyle.setAlignment(HorizontalAlignment.LEFT);
                        cell.setCellStyle(cellStyle);
                    }
                    switch (xlsField.getDataType()){
                        case  STRING:
                            cell.setCellValue(value.toString());
                            cellStyle.setAlignment(HorizontalAlignment.LEFT);
                            int rwsTemp =cell.getStringCellValue().split("\n").length;
                            if (rwsTemp > enterCnt) {
                                enterCnt = rwsTemp;
                                dataRow.setHeight((short)(enterCnt * 228));
                            }
                            break;
                        case  NUMERIC:
                            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                            BigDecimal cellvalue=BigDecimal.ZERO;
                            try {
                                cellvalue = new BigDecimal(value.toString());
                            }catch (Exception  err){

                            }
                            if (StringUtils.isNotBlank(xlsField.getFormat())) {
                                DecimalFormat format = new DecimalFormat(xlsField.getFormat());
                                String c1 = format.format(cellvalue);
                                cell.setCellValue(c1);
                            }else{
                                cell.setCellValue(cellvalue.doubleValue());
                            }
                            break;
                        case  DATETIME:
                            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                            BigDecimal bigDecimal=BigDecimal.ZERO;
                            try {
                                bigDecimal = new BigDecimal(value.toString());
                            }catch (Exception  err){

                            }
                            TimeZone utcTimeZone = TimeZone.getTimeZone(ZoneId.of("UTC"));
                            Date  datevalue= org.apache.poi.ss.usermodel.DateUtil.getJavaDate(bigDecimal.doubleValue(),utcTimeZone);
                            if (datevalue != null) {
                                if (StringUtils.isNotBlank(xlsField.getFormat())) {
                                    SimpleDateFormat format = new SimpleDateFormat(xlsField.getFormat());
                                    String  c1    = format.format(datevalue);
                                    cell.setCellValue(c1);
                                } else {
                                    cell.setCellValue(value.toString());
                                }
                            }


                            break;

                        case BOOLEAN:  //Boolean类型
                            boolean c2=(Boolean)value;
                            cell.setCellValue(c2);
                            cellStyle.setAlignment(HorizontalAlignment.CENTER);
                            break;

                        case FORMULA : //表达式类型
                            cell.setCellValue(value.toString());
                            cellStyle.setAlignment(HorizontalAlignment.LEFT);
                            break;

                        case ERROR: //异常类型 不知道何时算异常
                            cell.setCellValue("");
                            break;

                        case BLANK:  //空，不知道何时算空
                            value = "";
                            break;

                        default:
                            value = "";
                            break;
                    }
                    cell.setCellStyle(cellStyle);
                }
            }
            workbook.write(out);
            out.close();

        }catch (Exception err){
            err.printStackTrace();
        }
    }


}
